package com.ld.shieldsb.db.service.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.DynaBean;

import com.ld.shieldsb.common.core.collections.ListUtils;
import com.ld.shieldsb.common.core.model.Result;
import com.ld.shieldsb.common.core.util.StringUtils;
import com.ld.shieldsb.db.dao.dynamic.DynamicBaseDao;
import com.ld.shieldsb.db.model.DBSetInfo;
import com.ld.shieldsb.db.model.DBTableColumnModel;
import com.ld.shieldsb.db.model.DBTableModel;
import com.ld.shieldsb.db.service.DBTabelService;

import lombok.EqualsAndHashCode;
import lombok.extern.slf4j.Slf4j;

@Slf4j
@EqualsAndHashCode(callSuper = false)
/**
 * table服务oracle实现类
 *
 * @ClassName OracleTabelService
 * @author <a href="mailto:donggongai@126.com" target="_blank">吕凯</a>
 * @date 2019年1月23日 上午11:28:00
 *
 */
public class OracleTabelService extends DBTabelService {

    private static String TABLE_EXIST_SQL = "select * from user_tables where table_name =upper('%s')";

    static {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver"); // 注册驱动
        } catch (Exception e) {
            log.error("", e);
        } catch (Throwable e) {
            log.error("", e);
        }
    }

    public OracleTabelService(DBSetInfo dbSetInfo) {
        super(dbSetInfo);
    }

    @Override
    public List<DBTableModel> getTables(String name) {
        List<DBTableModel> tableList = new ArrayList<>();
        String strsql = " SELECT t.*,(SELECT COMMENTS FROM user_tab_comments utc WHERE utc.TABLE_NAME=t.TABLE_NAME) AS COMMENTS FROM all_tables t WHERE owner='"
                + dbSetInfo.getDbUserName().toUpperCase() + "' ";
        if (StringUtils.isNotBlank(name)) {
            strsql += " AND TABLE_NAME like ? ";
        }
        strsql += " ORDER BY table_name";
        ResultSet rsmd = null;
        try (Connection conn = DriverManager.getConnection(dbSetInfo.getDbUrl(), dbSetInfo.getDbUserName(), dbSetInfo.getDbPassword());
                PreparedStatement pstmt = conn.prepareStatement(strsql)) {
            if (StringUtils.isNotBlank(name)) {
                pstmt.setString(1, "%" + name.toUpperCase() + "%");
            }
            rsmd = pstmt.executeQuery();
//            rsmd = pstmt.getResultSet();

            while (rsmd.next()) {
                String tableName = rsmd.getString("TABLE_NAME");
                Long dataSize = rsmd.getLong("SAMPLE_SIZE");
                String comments = rsmd.getString("COMMENTS");
                DBTableModel model = new DBTableModel();
                model.setTableName(tableName);
                model.setDataSize(dataSize);
                model.setTableComments(comments);
                tableList.add(model);
            }
        } catch (Exception e) {
            log.error("查询用户" + dbSetInfo.getDbUserName() + "下的表出错！", e);
        } finally {
            if (rsmd != null) {
                try {
                    rsmd.close();
                } catch (SQLException e) {
                    log.error("关闭出错！", e);
                }
            }

        }
        return tableList;
    }

    @Override
    public String getAddColumnSql(DBTableColumnModel col, DBTableColumnModel beforeCol) {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public String getDropColumnSql(DBTableColumnModel col) {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public String getModifyColumnSql(DBTableColumnModel col, DBTableColumnModel beforeCol) {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public String getCreateTableSql(DBTableModel tableModel) {
        String createTableStr = null;
        String commentsStr = "";
        String tableComments = tableModel.getTableComments();
        String tableName = tableModel.getTableName().toUpperCase();
        if (ListUtils.isNotEmpty(tableModel.getColumns())) {
            List<String> primaryKeyList = new ArrayList<>();
            createTableStr = "CREATE TABLE \"" + tableName + "\" (\n";

            for (DBTableColumnModel column : tableModel.getColumns()) {
                String columnName = column.getColumnName().toUpperCase();
                String dataType = column.getDataType();

                createTableStr += "\"" + columnName + "\" " + dataType;
                String defaultVal = (String) column.getDefaultValue();
                if (StringUtils.isNotBlank(defaultVal)) {
                    if (!"CURRENT_TIMESTAMP".equalsIgnoreCase(defaultVal) && !"now()".equalsIgnoreCase(defaultVal)
                            && !defaultVal.endsWith("()") && !defaultVal.equalsIgnoreCase("sysdate")) { // 非函数型
                        createTableStr += " DEFAULT '" + defaultVal + "' ";
                    } else {
                        createTableStr += " DEFAULT sysdate ";
                    }
                }
                Boolean nullable = column.getNullable();
                if (nullable != null && !nullable) {
                    createTableStr += " NOT NULL";
                }
                String remarks = column.getRemarks();
                if (StringUtils.isNotBlank(remarks)) {
                    commentsStr += " COMMENT ON COLUMN \"" + tableName + "\".\"" + columnName + "\" IS '" + remarks + "';\n";
                }
                createTableStr += ",\n";
                Boolean ispk = column.getIsPrimaryKey();
                if (ispk != null && ispk) {
                    primaryKeyList.add(columnName);
                }
            }
            if (ListUtils.isNotEmpty(primaryKeyList)) {
                createTableStr += "PRIMARY KEY (\"" + StringUtils.join(primaryKeyList.toArray(new String[0]), "\",\"") + "\"),\n";
            }
            if (createTableStr.endsWith(",\n")) { // 去除多余的,号
                createTableStr = StringUtils.substring(createTableStr, 0, -2) + "\n";
            }
            createTableStr += ");\n";
            if (StringUtils.isNotBlank(tableComments)) {
                commentsStr += " COMMENT ON TABLE \"" + tableName + "\" IS '" + tableComments + "';";
            }
            createTableStr += commentsStr;
        }
        return createTableStr;
    }

    @Override
    public String getCreateTableSql(String tableName) {
        DynamicBaseDao baseDao = getDAO();

        String sql = "SELECT dbms_metadata.get_ddl('TABLE','" + tableName.toUpperCase() + "') AS ddlsql from dual";
        DynaBean bean = baseDao.getOne(sql);
        return bean.get("ddlsql").toString();
    }

    @Override
    public Result checkTableExist(String tableName) {

        Result result = new Result();

        DynamicBaseDao baseDao = getDAO();
        String strSql = String.format(TABLE_EXIST_SQL, tableName);
        List<Map<String, Object>> listMaps = baseDao.getSqlMapList(strSql);
        if (ListUtils.isNotEmpty(listMaps)) {
            result.setSuccess(true);
        } else {
            result.setMessage("表不存在");
        }

        return result;
    }

    @Override
    public Result createTable(String createTableStr) {
        Result result = new Result();
        DynamicBaseDao baseDao = getDAO();
        createTableStr = createTableStr.replace("r\n", " ").replace("\n", " ");
        String[] sqls = createTableStr.split(";");
        boolean execFlag = true;
        for (int i = 0; i < sqls.length; i++) {
            if (StringUtils.isNotEmpty(sqls[i]) && execFlag) {
                result = execSql(baseDao, sqls[i]);
                execFlag = result.getSuccess();
            }
        }
        return result;
    }

}
